<?php
// +----------------------------------------------------------------------
// | Description: 保有
// +----------------------------------------------------------------------
// | Author: zl
// +----------------------------------------------------------------------

namespace app\crm\controller;

use app\crm\model\ReserveandLost;
use app\admin\controller\ApiCommon;
use app\crm\model\Customer as CustomerModel;
use app\crm\model\CustomerConfig as CustomerConfigModel;
use think\response\Download;
use think\facade\Db;
use app\Request;

class CustomerRetain
{
    //公共条件
    public function public_where()
    {
        $today = date('Y-m-d', strtotime("-1 day"));
        $end_day = strtotime($today . " 23:59:59");
        return [$today,$end_day];
    }

    //---------------------------------------------------------------------------------------------------

    public function shopRetention($shopIds,$where=null,$level=null)
    {
        if(!empty($where))
        {
            $today = $where['today'];
            $end_day = $where['end_day'];
            $shopIds = (string)$shopIds;
        }else
        {
            list($today, $end_day) = $this->public_where();
        }
        $md_shop = DB::name('shop')
            ->where('status', 1)    // 运营中
            ->where('shop_type', 2) // 门店
            ->where('type', '<>', 2) // 除了加盟
            ->whereIn('shop_id', $shopIds)
            ->field('shop_id, shop_name, market_id, market_name')
            ->select()
            ->toArray();
        // var_dump($md_shop);die;
        $allInsertedData = [];
        // 门店 累计保有 - 累计流失
        foreach ($md_shop as $k => $vv)
        {
            $cities = array('石家庄','郑州','重庆','北京','天津','太原','成都','昆明','沈阳','济南','西安','长春');
            // 遍历城市列表进行匹配
            foreach ($cities as $city)
            {
                if (mb_strpos($vv["market_name"], $city) === 0)
                {
                    $group_name = $city;
                    break;
                }
            }

            $cuWhere = [
                ['shop_id', '=', $vv['shop_id']],
                ['cj_money', '>=', 500],            // 首单大于500
                ['customer_name', 'notlike', "%美团%"],
            ];

            $customer = DB::name('customer')->where($cuWhere)->column('id'); // 顾客
            
            // 滚动一年范围内在门店和门诊累计消费金额 根据客户分类
            $customer = $this->count_customer($customer, $today, $level);

            foreach ($customer as $key => $val)
            {
                // 保有 + 流失人数 |三个月前
                $baoyouList = $this->baoyou_customer($val, $today, $vv['shop_id'],strtotime($this->getLastDate($today, 3)));
                $baoyou = $baoyouList['by'];
                $lost = $baoyouList['ls'];
                $ljby_count = count($baoyou); // 累计保有人数
                $ljls_count = count($baoyouList['ls']); // 累计流失人数


                //一个月前 获取保有的流失预警数
                $lostWarning = $this->baoyou_customer(implode(',',$baoyou), $today, $vv['shop_id'],strtotime($this->getLastDate($today)));
                //计算流失预警率
                $lostWarningCount = count($lostWarning['ls']);
                $lostWarningIds = implode(',', $lostWarning['ls']);
                
                // 查询昨天的流失预警人员，如果有计算流失预警率
                $twoDayList = Db::name('shop_retention')
                    ->where('date', date('Y-m-d', strtotime($today . ' -1 days')))
                    ->where('shop_id', $vv['shop_id'])
                    ->where('level', $key)
                    ->value('lost_warning_ids');

                // 重置相关变量
                $rIds = [];
                $reflux_count = 0;
                $reflux_ids = '';

                if (is_string($twoDayList) && !empty($twoDayList))
                {
                    $twoDayCount = explode(",", $twoDayList);

                    // 检查 $twoDayCount 是否为数组
                    if (is_array($twoDayCount))
                    {
                        $twoDayCountNum = count($twoDayCount);

                        // 查询昨天的流失预警人员是否回流
                        foreach ($twoDayCount as $lv)
                        {
                            $huiliu = $this->baoyou_customer($lv, $today, $vv['shop_id'], strtotime($today.' -1 day'));  // 昨日 查询是否回流
                            if (is_array($huiliu['by']))
                            {
                                // 将 $huiliu['by'] 数组中的元素合并到 $rIds 中
                                $rIds = array_merge($rIds, $huiliu['by']);
                            }
                            // 假设 $huiliu['by'] 是一个数组，检查 $lv 是否在其中
                            if (in_array($lv, $huiliu['by']))
                            {
                                $reflux_count++;
                            }
                        }
                        $reflux_ids = implode(',', $rIds);

                        // 高精度计算流失预警率，使用三元运算符处理除数为 0 的情况
                        $lost_warning_rate = $twoDayCountNum > 0 ? bcmul(bcdiv($reflux_count, $twoDayCountNum, 4), 100, 2) : '0.00';
                    }else
                    {
                        $lost_warning_rate = '0.00';
                    }
                }else
                {
                    $lost_warning_rate = '0.00';
                }

                //门店 门诊 年度 消费金额
                $customerIds = implode(',', array_filter([implode(',',$baoyou),implode(',',$lost)]));
                $amount = $this->amount_customer($customerIds, $vv['shop_id'], $today);
                $gongxian = $ljby_count? bcdiv(bcadd(bcdiv($amount['mz_amount'], '2', 2), $amount['md_amount'], 2), $ljby_count, 2) : 0;

                $customerCount = $ljby_count + $ljls_count; // A类型客户总数
                $ljby_rete = $customerCount > 0 ? bcmul(bcdiv($ljby_count, $customerCount, 4), 100, 2) : 0;  // 累计保有率
                $ljls_rete = $customerCount > 0 ? bcmul(bcdiv($ljls_count, $customerCount, 4), 100, 2) : 0;  // 累计流失率

                $aclass_total_install = [
                    'date' => $today,
                    'market_id' => $vv['market_id'],
                    'market_name' => $vv['market_name'],
                    'group_name' => $group_name,
                    'shop_id' => $vv['shop_id'],
                    'shop_name' => $vv['shop_name'],
                    'level' => $key,
                    'total_count' => $customerCount,
                    'retained_count' => $ljby_count,
                    'lost_count' => $ljls_count,
                    'retention_rate' => $ljby_rete,
                    'lost_rate' => $ljls_rete,
                    'retained_ids' => empty($baoyou) ? '' : implode(',', $baoyou),
                    'lost_ids' => empty($lost) ? '' : implode(',', $lost),
                    'lost_warning' => $lostWarningCount,
                    'lost_warning_rate' => $lost_warning_rate,
                    'lost_warning_ids' => $lostWarningIds,
                    'reflux_count' => $reflux_count??0,
                    'reflux_ids' => $reflux_ids??null,
                    'year_amount' => $amount['year_amount'],
                    'md_amount' => $amount['md_amount'],
                    'mz_amount' => $amount['mz_amount'],
                    'gongxian' => $gongxian,
                    'create_time' => date('Y-m-d H:i:s')
                ];

                // 检查之前有无这天的这个门店的数据
                $existingData = Db::name('shop_retention')->where(['date' => $today,'level' => $key,'shop_id' => $vv['shop_id']])->find();
                if($existingData)
                {
                    // 存在旧数据，将旧数据迁移到 shop_retention_befor 表
                    $migrationData = $existingData;
                    $migrationData['retention_id'] = $migrationData['id'];
                    $migrationData['create_time'] = date('Y-m-d H:i:s');
                    unset($migrationData['id']);
                    unset($migrationData['update_num']);
                    Db::name('shop_retention_befor')->insert($migrationData);

                    // 更新 shop_retention 表中的旧数据
                    Db::name('shop_retention')->where('id', $existingData['id'])->update($aclass_total_install);

                    // 自增 update_num 字段
                    Db::name('shop_retention')->where('id', $existingData['id'])->inc('update_num', 1)->update();

                }else
                {
                    // 不存在旧数据，插入新数据
                    Db::name('shop_retention')->insert($aclass_total_install);
                }
            }
        }
        if(empty($where))
        {
            echo $today."-处理完成";exit;
        }
    }
    
    
    public function shopRetentionCrontab(Request $request)
    {
        $param = $request->param();
        if(!empty($param['shop_id']))
        {
            if(!empty($param['today']) && !empty($param['end_day']))
            {
               $where = ['today' => $param['today'], 'end_day' => $param['end_day']];
            }else
            {
                $where = null;
            }
            if(!empty($param['level']))
            {
                $this->shopRetention($param['shop_id'],$where,$param['level']);
            }else
            {
                $this->shopRetention($param['shop_id'],$where);
            }
        }
    }
    //---------------------------------------------------------------------------------------------------

    //先把数据 相同店铺 相同创建时间的记录整理好
    public function organizeData()
    {
        list($today, $end_day) = $this->public_where();
        
        // 查询 create_time 相同 和 shop_id 相同的数据 date 最早的一条
        $sql = "SELECT t.* 
                FROM xy_shop_retention_log t
                JOIN (
                    SELECT shop_id, MIN(date) as min_date
                    FROM xy_shop_retention_log
                    WHERE create_time = :today1 AND status = 0
                    GROUP BY shop_id
                ) sub ON t.shop_id = sub.shop_id AND t.date = sub.min_date
                WHERE t.create_time = :today2
                GROUP BY t.shop_id";
        
        $list = Db::query($sql, ['today1' => $today, 'today2' => $today]);
        
        // 获取需要更新的 ID 列表
        $updateIds = array_column($list, 'id');
        // 执行修改
        $res = Db::name('shop_retention_log')->where('id', 'in', $updateIds)->update(['status' => 1]);
        
        if($res)
        {
            echo "处理完成";exit;
        }

    }
    //每日查询是否有今日之前的成交记录
    public function checkBeforRetention()
    {
        list($today, $end_day) = $this->public_where();

        $list = Db::name('shop_retention_log')->where('create_time',$today)->where('status',1)->select()->toArray();
        // 假设 $list 是从数据库查询出来的包含店铺记录的数组
        $numCount = 0;
        foreach ($list as $item)
        {
            $id = $item['id'];
            $shopId = $item['shop_id'];
            $startDateStr = $item['date'];
            $createTimeStr = $item['create_time'];

            $startDate = new \DateTime($startDateStr);
            $createTime = new \DateTime($createTimeStr);

            $dateInterval = $startDate->diff($createTime);
            $totalDays = $dateInterval->days +1;
            $daysToProcess = min($totalDays, 3);//3条
            $currentDate = clone $startDate;
            $processedDates = [];

            for ($i = 0; $i < $daysToProcess; $i++)
            {
                $processedDates[] = $currentDate->format('Y-m-d');
                $currentDate->modify('+1 day');
            }

            foreach ($processedDates as $date)
            {
                // 先处理逻辑，再增加计数
                $endDayTimestamp = strtotime($date . ' 23:59:59');
                $this->shopRetention($shopId, ['today' => $date, 'end_day' => $endDayTimestamp]);
                $numCount += 1;
            }
            
            $remainingDays = $totalDays - $daysToProcess;
            
            if ($remainingDays > 0)
            {
                $newStartDate = (clone $startDate)->modify("+$daysToProcess days")->format('Y-m-d');
                Db::table('xy_shop_retention_log')->where('id', $id)->update(['date' => $newStartDate, 'status' => 1]);
            }else
            {   
                Db::table('xy_shop_retention_log')->where('id', $id)->update(['status' => 2]);
            }

            // 检查是否达到3次
            if ($numCount >= 3)
            {
                echo "处理完成";exit;
            }
        }
        echo "处理完成";exit;
    }

    //--------------------------------------------------------------------------------------------------

    //滚动一年范围内在门店和门诊累计消费金额大于等于50000
    public function count_customer($customerIds,$today, $level=null)
    {
        $start_time =  strtotime($today.' -365 day');
        $end_time = strtotime($today);
        $telArray = Db::name('customer')->where('id','in',$customerIds)->column('telephone');
        //查询门诊用户
        $menzhenArray =  Db::name('customer')->alias('c')
            ->join('shop s', 'c.shop_id=s.shop_id', 'LEFT')
            ->where('c.customer_name', 'notlike', '%美团%')
            ->where('c.status', 1)
            ->where('c.telephone', 'in', $telArray)
            ->where('s.shop_type', 1)
            ->column('c.id');
        if (!is_array($customerIds))
        {
            $customerIds = explode(',', (string)$customerIds);
        }
        $customerIdsArray = array_merge($customerIds,$menzhenArray);

        // 映射数存储每个电话号码对应的客户 ID 数组
        $telToIdsMap = [];
        foreach ($customerIdsArray as $id)
        {
            $tel = Db::name('customer')->where('id', $id)->value('telephone');
            if (!isset($telToIdsMap[$tel]))
            {
                $telToIdsMap[$tel] = [];
            }
            $telToIdsMap[$tel][] = $id;
        }
        // 构建收款单查询条件
        $whereShoukuan = [
            ['status', '=', 2],
            ['uid', 'in', $customerIdsArray],
            ['type', '=', 3],
            ['refund_type', '=', '101.02'],
            ['deal_time', 'between', "$start_time,$end_time"]
        ];
        // 构建退款单查询条件
        $whereTuikuan = [
            ['status', '=', 2],
            ['uid', 'in', $customerIdsArray],
            ['type', '=', 1],
            ['refund_type', '<>', '101.03'],
            ['deal_time', 'between', "$start_time,$end_time"]
        ];

        // 分别生成收款单和退款单的 SQL 语句
        $shoukuanSql = Db::name('shop_refund_order')
            ->where($whereShoukuan)
            ->field('uid, type, sum(pay_amount + shop_transfer_amount) as amount')
            ->group('uid, type')
            ->fetchSql(true)
            ->select();

        $tuikuanSql = Db::name('shop_refund_order')
            ->where($whereTuikuan)
            ->field('uid, type, sum(pay_amount + shop_transfer_amount) as amount')
            ->group('uid, type')
            ->fetchSql(true)
            ->select();
        // 手动构建 UNION SQL 语句
        $unionSql = "({$shoukuanSql}) UNION ({$tuikuanSql})";
        // 执行 SQL 语句
        $orders = Db::query($unionSql);
        

        // 计算每个电话号码对应的总金额
        $telTotalAmounts = [];
        foreach ($orders as $order)
        {
            $uid = $order['uid'];
            $amount = $order['amount'];
            $tel = Db::name('customer')->where('id', $uid)->value('telephone');
            if (!isset($telTotalAmounts[$tel]))
            {
                $telTotalAmounts[$tel] = 0;
            }
            if ($order['type'] == 3) { // 收款单
                $telTotalAmounts[$tel] += $amount;
            } elseif ($order['type'] == 1) { // 退款单
                $telTotalAmounts[$tel] -= $amount;
            }
        }

        // 将每个电话号码对应的总金额分配给该电话号码关联的所有客户 ID
        $totalAmounts = [];
        foreach ($telToIdsMap as $tel => $ids)
        {
            $totalAmount = isset($telTotalAmounts[$tel]) ? $telTotalAmounts[$tel] : 0;
            foreach ($ids as $id)
            {
                $totalAmounts[$id] = $totalAmount;
            }
        }
        // 初始化每个等级对应的客户 ID 数组
        
        if(!empty($level))
        {
            foreach ($customerIdsArray as $uid)
            {
                $totalAmount = isset($totalAmounts[$uid]) ? $totalAmounts[$uid] : 0;
                if ($level === 'A')
                {
                    switch (true)
                    {
                        case ($totalAmount >= 50000):
                            $mergedData['A'][] = $uid;
                            break;
                        case ($totalAmount < 50000 && $totalAmount >= 15000):
                            $mergedData['B'][] = $uid;
                            break;
                        case ($totalAmount < 15000 && $totalAmount >= 5000):
                            $mergedData['C'][] = $uid;
                            break;
                        case ($totalAmount < 5000 && $totalAmount >= 500):
                            $mergedData['D'][] = $uid;
                            break;
                    }
                }elseif ($level === 'E')
                {
                    if ($totalAmount < 500)
                    {
                        $mergedData['E'][] = $uid;
                    }
                }
            }
        }else
        {
            foreach ($customerIdsArray as $uid) 
            {
                $totalAmount = isset($totalAmounts[$uid]) ? $totalAmounts[$uid] : 0;
                switch (true)
                {
                    case ($totalAmount >= 50000):
                        $mergedData['A'][] = $uid;
                        break;
                    case ($totalAmount < 50000 && $totalAmount >= 15000):
                        $mergedData['B'][] = $uid;
                        break;
                    case ($totalAmount < 15000 && $totalAmount >= 5000):
                        $mergedData['C'][] = $uid;
                        break;
                    case ($totalAmount < 5000 && $totalAmount >= 500):
                        $mergedData['D'][] = $uid;
                        break;
                    case ($totalAmount < 500):
                        $mergedData['E'][] = $uid;
                        break;
                }
            }
        }
        // 返回以 ABCDE 为分组的客户 ID 数组
        return $mergedData;
    }

    //年度保有消费
    public function amount_customer($customerIds,$shop_id,$today)
    {
        $res['md_amount'] = 0;
        $res['mz_amount'] = 0;
        $res['year_amount'] = 0;
        $start_time =  strtotime($today.' -365 day');
        $end_time = strtotime($today);

        $telArray = Db::name('customer')->where('id','in',$customerIds)->column('telephone');
        //查询门诊用户
        $menzhenArray =  Db::name('customer')->alias('c')
            ->join('shop s', 'c.shop_id=s.shop_id', 'LEFT')
            ->where('c.customer_name', 'notlike', '%美团%')
            ->where('c.status', 1)
            ->where('c.telephone', 'in', $telArray)
            ->where('s.shop_type', 1)
            ->column('c.id');
        if (!is_array($customerIds))
        {
            $customerIds = explode(',', (string)$customerIds);
        }
        $customerIdsArray = array_merge($customerIds,$menzhenArray);



        // 初始化 $md 和 $mz 列表
        $md = [];
        $mz = [];
        // 根据 shop_id 进行分类
        foreach ($customerIdsArray as $id)
        {
            // 查询该客户 ID 对应的 shop_id
            $shopIdOfRelated = Db::name('customer')->where('id', $id)->value('shop_id');
            if ($shopIdOfRelated == $shop_id)
            {
                $md[] = $id;
            } else {
                $mz[] = $id;
            }
        }

        // 计算门店收款和退款总和
        $md_sum = Db::name('shop_refund_order')
            ->where([
                ['status', '=', 2], // 已下推
                ['uid', 'in', $md],
                ['deal_time', 'between', [$start_time, $end_time]]
            ])
            ->field([
                // 计算收款金额总和，使用 pay_amount + shop_transfer_amount
                "SUM(CASE WHEN `type` = 3 AND `refund_type` = '101.02' THEN pay_amount + shop_transfer_amount ELSE 0 END) as total_shoukuan",
                // 计算退款金额总和，使用 pay_amount + shop_transfer_amount
                "SUM(CASE WHEN `type` = 1 AND `refund_type` <> '101.03' THEN pay_amount + shop_transfer_amount ELSE 0 END) as total_tuikuan"
            ])
            ->find();

        // 使用 bcsub 进行高精度减法运算
        $res['md_amount'] = bcsub($md_sum['total_shoukuan'], $md_sum['total_tuikuan'], 2); // 保留 2 位小数

        // 计算门诊收款和退款总和
        $mz_sum = Db::name('shop_refund_order')
            ->where([
                ['status', '=', 2], // 已下推
                ['uid', 'in', $mz],
                ['deal_time', 'between', [$start_time, $end_time]]
            ])
            ->field([
                // 计算收款金额总和，使用 pay_amount + shop_transfer_amount
                "SUM(CASE WHEN `type` = 3 AND `refund_type` = '101.02' THEN pay_amount + shop_transfer_amount ELSE 0 END) as total_shoukuan",
                // 计算退款金额总和，使用 pay_amount + shop_transfer_amount
                "SUM(CASE WHEN `type` = 1 AND `refund_type` <> '101.03' THEN pay_amount + shop_transfer_amount ELSE 0 END) as total_tuikuan"
            ])
            ->find();

        // 使用 bcsub 进行高精度减法运算
        $res['mz_amount'] = bcsub($mz_sum['total_shoukuan'], $mz_sum['total_tuikuan'], 2); // 保留 2 位小数

        // 使用 bcadd 进行高精度加法运算
        $res['year_amount'] = bcadd($res['md_amount'], $res['mz_amount'], 2); // 保留 2 位小数
        // 输出结果
        return $res;

    }
    
    
    // 获取累计 周期性保有-流失 + 流失预警
    public function baoyou_customer($customerIds,$today,$shop_id,$start_time)
    {
        $by = [];
        $ls = [];
        $end_time = strtotime($today.' 23.59.59');

        $telArray = Db::name('customer')->where('id','in',$customerIds)->column('telephone');
        //查询门诊用户
        $menzhenArray =  Db::name('customer')->alias('c')
            ->join('shop s', 'c.shop_id=s.shop_id', 'LEFT')
            ->where('c.customer_name', 'notlike', '%美团%')
            ->where('c.status', 1)
            ->where('c.telephone', 'in', $telArray)
            ->where('s.shop_type', 1)
            ->column('c.id');
        if (!is_array($customerIds))
        {
            $customerIds = explode(',', (string)$customerIds);
        }
        $customerIds = array_merge($customerIds,$menzhenArray);
        
        //收退款记录
        $refundWhere = [
            ['uid', 'in', $customerIds], // 用户
            ['deal_time', 'between', "$start_time,$end_time"], // 三个月
            ['status', '=', 2], // 已下推
            ['type', '=', 3], // 收款单
            ['refund_type', '=', '101.02'] // 收款
        ];

        // 查询 shop_refund_order 表中符合条件的用户 ID
        $shop_refund_order_customer_id = DB::name('shop_refund_order')->where($refundWhere)->column('uid');
        
        $orderWhere = [
            ['uid', 'in', $customerIds], // 用户
            ['deal_time', 'between', "$start_time,$end_time"], // 三个月
            ['status', '=', 2], // 已下推
            ['is_pay', '=', 1], // 已支付
            ['total_amount', '>', 0], // 金额 > 0
        ];

        // 查询 shop_order 表中符合条件的用户 ID
        $shop_order_customer_id = DB::name('shop_order')->where($orderWhere)->column('uid');

        $serviceWhere = [
            ['customer_id', 'in', $customerIds],
            ['create_time', 'between', "$start_time,$end_time"],
            ['is_cancel', '<>', 2],
        ];

        // 查询 service_log 表中符合条件的用户 ID
        $service_customer_id = DB::name('service_log')->where($serviceWhere)->column('customer_id');

        // 合并三个表中符合条件的用户 ID
        $all_matched_customer_ids = array_unique(array_merge(
            $shop_refund_order_customer_id,
            $shop_order_customer_id,
            $service_customer_id
        ));
        // 计算保有 by 关系的用户 ID
        $by = array_intersect($customerIds, $all_matched_customer_ids);
        // 计算不符合条件的用户 ID
        $ls = array_diff($customerIds, $all_matched_customer_ids);
        //处理流失数据
        $validLs = [];
        foreach ($ls as $id)
        {
            $currentShopId = Db::name('customer')->where('id', $id)->value('shop_id');
            if ($currentShopId == $shop_id)
            {
                $validLs[] = $id;
            }
        }
        $ls = $validLs;
        
       // 存储每个 id 对应的手机号和 shop_id
        $idInfo = [];
        foreach ($by as $id)
        {
            $info = Db::name('customer')->where('id', $id)->field('telephone, shop_id')->find();
            if ($info)
            {
                $idInfo[$id] = $info;
            }
        }
        // 存储手机号到 id 的映射，用于后续替换
        $telephoneToId = [];
        foreach ($idInfo as $id => $info)
        {
            $telephone = $info['telephone'];
            if (!isset($telephoneToId[$telephone]))
            {
                $targetId = $this->get_id_by_telephone_and_shop_id($telephone, $shop_id);
                if ($targetId)
                {
                    $telephoneToId[$telephone] = $targetId;
                }else
                {
                    $telephoneToId[$telephone] = $id;
                }
            }
        }
        // 替换 by 和 ls 中的 id
        foreach ($by as &$id)
        {
            $id = $telephoneToId[$idInfo[$id]['telephone']];
        }
        unset($id); // 释放引用
        
        // 去重
        $by = array_unique($by);
        $ls = array_unique($ls);
        $ls = array_diff($ls, $by);
        
        return ['by' => $by, 'ls' => $ls];
    }


    //对应的手机号
    public function get_id_by_telephone_and_shop_id($telephone, $shop_id)
    {
        return Db::name('customer')
            ->where('telephone', $telephone)
            ->where('shop_id', $shop_id)
            ->value('id');
    }

    //ID合并
    function filterIdsByShopId($ids, $shop_id)
    {
        $valid_ids = [];
        if(!empty($ids) &&is_array($ids))
        {
            foreach($ids as $id)
            {
                // 获取该 id 对应的手机号
                $telephone = Db::name('customer')->where('id', $id)->value('telephone');
                if($telephone)
                {
                    // 根据手机号查找所有相关记录
                    $relatedRecords = Db::name('customer')->field('id,shop_id,telephone')->where('telephone', $telephone)->select();
                    
                    // 筛选出 shop_id 等于指定值的记录
                    foreach ($relatedRecords as $record)
                    {
                        if ($record['shop_id'] == $shop_id)
                        {
                            $valid_ids[] = $record['id'];
                        }
                    }
                }
            }
        }
        return $valid_ids;
    }
    
    
    function getLastDate($dateString, $monthsToSubtract = 1)
    {
        $date = new \DateTime($dateString);  // 使用 \DateTime 确保从全局加载
        $originalDay = (int)$date->format('d');
        $year = (int)$date->format('Y');
        $month = (int)$date->format('m');
    
        // 计算目标月份和年份（支持跨年）
        $targetMonth = $month - $monthsToSubtract;
        $targetYear = $year;
    
        // 处理跨年情况（如 2025-01-01 往前推 3 个月是 2024-10-01）
        while ($targetMonth < 1) {
            $targetMonth += 12;
            $targetYear--;
        }
    
        // 获取目标月份的最后一天
        $targetDate = new \DateTime();  // 使用 \DateTime
        $targetDate->setDate($targetYear, $targetMonth, 1);
        $targetDate->setTime(0, 0, 0);
        $lastDayOfTargetMonth = (int)$targetDate->format('t');
    
        // 确定正确的天数（如果原日期的天数超过目标月份的最后一天，则取最后一天）
        $adjustedDay = min($originalDay, $lastDayOfTargetMonth);
    
        // 创建调整后的日期
        $adjustedDate = new \DateTime();  // 使用 \DateTime
        $adjustedDate->setDate($targetYear, $targetMonth, $adjustedDay);
        $adjustedDate->setTime(0, 0, 0);
    
        return $adjustedDate->format('Y-m-d');
    }

    //--------------------------------------------------------------------------------------------------
    public function getCardCorrectList(Request $request)
	{
	    $param = $request->param();
	    
	    $result = Db::name('customer')->alias('c')
        ->field('s.market_name, s.shop_name, c.customer_name, c.telephone, sp.name')
        ->join('shop s', 'c.shop_id = s.shop_id')
        ->join('shop_person sp', 'c.nurse_id = sp.id')
        ->where('c.card_is_correct', 1)
        ->where('s.shop_name', $param['shop_name'])
        ->select()->toArray();
	    
        $excelName = $param['shop_name']."-卡项正确用户表";
            
        $callName = [
            'market_name' => '市场',
            'shop_name' => '门店',
            'customer_name' => '用户姓名',
            'telephone' => '手机号',
            'name' => '护理师'
        ];
        
        $downUrl = exportExcelData($excelName,$callName,$result,'xlsx',1);
        
        echo "<script language='javascript'>";

		echo " location='".$downUrl."';";

		echo "</script>";
		
        exit;
	}

}
